﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SQLite;

namespace NVRCsharpDemo
{
    class DbWorker
    {
        private DbProviderFactory fact = DbProviderFactories.GetFactory("System.Data.SQLite.EF6");
        private String connectionName = System.Configuration.ConfigurationManager.ConnectionStrings["sqlite"].ConnectionString;
        public DbWorker()
        {
        }

        public int Insert(String plateNumber, String vehicleType, String enterTime)
        {
            using (DbConnection conn = fact.CreateConnection())
            {
                conn.ConnectionString = connectionName;
                conn.Open();
                DbTransaction trans = conn.BeginTransaction();
                try
                {
                    DbCommand dbCommand = conn.CreateCommand();
                    dbCommand.CommandType = CommandType.Text;
                    dbCommand.CommandText = "Insert into car_info (plate_number, vehicle_type, enter_time, enable) "
                        + "values (@plateNumber, @vehicleType, @enterTime, @enable)";
                    SQLiteParameter[] parameters = new SQLiteParameter[4];
                    dbCommand.Parameters.Add(new SQLiteParameter("@plateNumber", plateNumber));
                    dbCommand.Parameters.Add(new SQLiteParameter("@vehicleType", vehicleType));
                    dbCommand.Parameters.Add(new SQLiteParameter("@enterTime", enterTime));
                    dbCommand.Parameters.Add(new SQLiteParameter("@enable", 1));
                    return dbCommand.ExecuteNonQuery();
                }
                catch (Exception e)
                {
                    trans.Rollback();
                }
                finally
                {
                    trans.Commit();
                }
                return -1;
            }
        }

        public int Update(String plateNumber, String exitTime)
        {
            using (DbConnection conn = fact.CreateConnection())
            {
                conn.ConnectionString = connectionName;
                conn.Open();
                DbTransaction trans = conn.BeginTransaction();
                try
                {
                    DbCommand dbCommand = conn.CreateCommand();
                    dbCommand.CommandType = CommandType.Text;
                    dbCommand.CommandText = "UPDATE car_info SET exit_time = @exitTime, enable = 0" +
                        " WHERE plate_number = @plateNumber AND enable = 1";
                    dbCommand.Parameters.Add(new SQLiteParameter("@plateNumber", plateNumber));
                    dbCommand.Parameters.Add(new SQLiteParameter("@exitTime", exitTime));
                    int rs = dbCommand.ExecuteNonQuery();
                    return rs;
                }
                catch (Exception e)
                {
                    trans.Rollback();
                }
                finally
                {
                    trans.Commit();
                }
                return -1;
            }
        }

        public List<CarInfo> QueryExistedCar(int start, int size)
        {
            using (DbConnection conn = fact.CreateConnection())
            {
                conn.ConnectionString = connectionName;
                conn.Open();
                DbCommand comm = conn.CreateCommand();
                comm.CommandText = "SELECT id, plate_number, vehicle_type, enter_time, exit_time"
                    + " FROM car_info"
                    + " WHERE enable = 1"
                    + " ORDER BY id desc"
                    + " limit @start, @size";
                comm.CommandType = CommandType.Text;
                comm.Parameters.Add(new SQLiteParameter("@start", start));
                comm.Parameters.Add(new SQLiteParameter("@size", size));
                List<CarInfo> carInfos = new List<CarInfo>();
                using (IDataReader reader = comm.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        CarInfo car = new CarInfo(reader["plate_number"].ToString(),
                            reader["vehicle_type"].ToString(),
                            reader["enter_time"].ToString())
                        {
                            id = reader.GetInt32(0)
                        };
                        if (reader["exit_time"] != null)
                        {
                            car.exitTime = reader["exit_time"].ToString();
                        }
                        carInfos.Add(car);
                    }
                }
                return carInfos;
            }
        }
        public int QuerySmallCarCount()
        {
            return QueryExistCarCount("小型车");
        }

        public int QueryBigCarCount()
        {
            return QueryExistCarCount("大型车");
        }

        public int QueryExistCarCount(String vehicleType)
        {
            using (DbConnection conn = fact.CreateConnection())
            {
                conn.ConnectionString = connectionName;
                conn.Open();
                DbCommand comm = conn.CreateCommand();
                String sql = "SELECT count(*) AS total"
                    + " FROM car_info"
                    + " WHERE enable = 1";
                if (vehicleType != null)
                {
                    sql += " AND vehicle_type = @vehicleType";
                    comm.Parameters.Add(new SQLiteParameter("@vehicleType", vehicleType));
                }
                comm.CommandText = sql;
                comm.CommandType = CommandType.Text;
                using (IDataReader reader = comm.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        return reader.GetInt32(0);
                    }
                }
                return 0;
            }
        }

        public CarInfo Get(int id)
        {
            using (DbConnection conn = fact.CreateConnection())
            {
                conn.ConnectionString = connectionName;
                conn.Open();
                DbCommand comm = conn.CreateCommand();
                comm.CommandText = "SELECT id, plate_number, vehicle_type, enter_time, exit_time"
                    + " FROM car_info"
                    + " WHERE enable = 1"
                    + " AND id = @id";
                comm.Parameters.Add(new SQLiteParameter("@id", id));
                comm.CommandType = CommandType.Text;
                using (IDataReader reader = comm.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        CarInfo car = new CarInfo(reader["plate_number"].ToString(),
                            reader["vehicle_type"].ToString(),
                            reader["enter_time"].ToString())
                        {
                            id = reader.GetInt32(0)
                        };
                        if (reader["exit_time"] != null)
                        {
                            car.exitTime = reader["exit_time"].ToString();
                        }
                        return car;
                    }
                }
                return null;
            }
        }

        internal void Reset()
        {
            using (DbConnection conn = fact.CreateConnection())
            {
                conn.ConnectionString = connectionName;
                conn.Open();
                DbTransaction trans = conn.BeginTransaction();
                try
                {
                    DbCommand dbCommand = conn.CreateCommand();
                    dbCommand.CommandType = CommandType.Text;
                    dbCommand.CommandText = "UPDATE car_info SET exit_time = @exitTime, enable = 0" +
                        " WHERE enable = 1";
                    dbCommand.Parameters.Add(new SQLiteParameter("@exitTime", DateTime.Now.ToString("s")));
                    dbCommand.ExecuteNonQuery();
                }
                catch (Exception e)
                {
                    trans.Rollback();
                }
                finally
                {
                    trans.Commit();
                }
            }
        }
    }
}
